package com.javaforever.dictionary.daoimpl;

import com.javaforever.dictionary.dao.DictionaryDao;
import com.javaforever.dictionary.domain.Dictionary;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class DictionaryDaoImpl implements DictionaryDao{
	public List<Dictionary> listAllDictionarys(Connection connection) throws Exception{
		try {
			String query = "select parent_id,dic_group_id,dictionary_key,dictionary_value,description,dictionary_name,active,dictionary_id from dic_dictionarys;";
			PreparedStatement ps = connection.prepareStatement(query);
			ResultSet result = ps.executeQuery();
			ArrayList<Dictionary> list = new ArrayList<Dictionary>();
			while(result.next()) {
				//Build the list object.
				Dictionary dictionary = new Dictionary();
				dictionary.setParentId(result.getLong("parent_id"));
				dictionary.setDicGroupId(result.getLong("dic_group_id"));
				dictionary.setDictionaryKey(result.getString("dictionary_key"));
				dictionary.setDictionaryValue(result.getString("dictionary_value"));
				dictionary.setDescription(result.getString("description"));
				dictionary.setDictionaryName(result.getString("dictionary_name"));
				dictionary.setActive(result.getBoolean("active"));
				dictionary.setDictionaryId(result.getLong("dictionary_id"));
				//Build the object list.
				list.add(dictionary);
			}
			return list;
		} catch (Exception e){
			e.printStackTrace();
			return new ArrayList<Dictionary>();
		}
	}

	public boolean updateDictionary(Connection connection,Dictionary dictionary) throws Exception{
		String query = "update dic_dictionarys set parent_id = ? ,dic_group_id = ? ,dictionary_key = ? ,dictionary_value = ? ,description = ? ,dictionary_name = ? ,active = ? where dictionary_id = ?;";
		PreparedStatement ps = connection.prepareStatement(query);
		ps.setLong(1,dictionary.getParentId());
		ps.setLong(2,dictionary.getDicGroupId());
		ps.setString(3,dictionary.getDictionaryKey());
		ps.setString(4,dictionary.getDictionaryValue());
		ps.setString(5,dictionary.getDescription());
		ps.setString(6,dictionary.getDictionaryName());
		ps.setBoolean(7,dictionary.getActive());
		ps.setLong(8,dictionary.getDictionaryId());
		int result = ps.executeUpdate();
		if (result > 0) {
			return true;
		}
		return false;
	}

	public boolean deleteDictionary(Connection connection,Long dictionaryId) throws Exception{
		String query = "delete from dic_dictionarys where dictionary_id = ?;";
		PreparedStatement ps = connection.prepareStatement(query);
		ps.setLong(1,dictionaryId);
		int result = ps.executeUpdate();
		if (result > 0) {
			return true;
		}
		return false;
	}

	public boolean addDictionary(Connection connection,Dictionary dictionary) throws Exception{
		String query = "insert into dic_dictionarys ( parent_id,dic_group_id,dictionary_key,dictionary_value,description,dictionary_name,active) values (?,?,?,?,?,?,?);";
		PreparedStatement ps = connection.prepareStatement(query,PreparedStatement.RETURN_GENERATED_KEYS);
		ps.setLong(1,dictionary.getParentId());
		ps.setLong(2,dictionary.getDicGroupId());
		ps.setString(3,dictionary.getDictionaryKey());
		ps.setString(4,dictionary.getDictionaryValue());
		ps.setString(5,dictionary.getDescription());
		ps.setString(6,dictionary.getDictionaryName());
		ps.setBoolean(7,dictionary.getActive());
		int result = ps.executeUpdate();
		ResultSet rsNewId = ps.getGeneratedKeys();
		if (rsNewId.next()) { 
			long newId = rsNewId.getLong(1);
			dictionary.setDictionaryId(newId);
		}
		if (result > 0) {
			return true;
		}
		return false;
	}

	public boolean softDeleteDictionary(Connection connection,Long dictionaryId) throws Exception{
		String query = "update dic_dictionarys set active = false where dictionary_id = ? ;";
		PreparedStatement ps = connection.prepareStatement(query);
		ps.setLong(1,dictionaryId);
		int result = ps.executeUpdate();
		if (result > 0) {
			return true;
		}
		return false;
	}

	public Dictionary findDictionaryByDictionaryId(Connection connection,Long dictionaryId) throws Exception{
		try {
			String query = "select parent_id,dic_group_id,dictionary_key,dictionary_value,description,dictionary_name,active,dictionary_id from dic_dictionarys where dictionary_id = ?;";
			PreparedStatement ps = connection.prepareStatement(query);
			ps.setLong(1,dictionaryId);
			ResultSet result = ps.executeQuery();
			Dictionary dictionary = new Dictionary();
			result.last();
			//Build the object.
			dictionary.setParentId(result.getLong("parent_id"));
			dictionary.setDicGroupId(result.getLong("dic_group_id"));
			dictionary.setDictionaryKey(result.getString("dictionary_key"));
			dictionary.setDictionaryValue(result.getString("dictionary_value"));
			dictionary.setDescription(result.getString("description"));
			dictionary.setDictionaryName(result.getString("dictionary_name"));
			dictionary.setActive(result.getBoolean("active"));
			dictionary.setDictionaryId(result.getLong("dictionary_id"));
			return dictionary;
		} catch (Exception e){
			e.printStackTrace();
			return null;
		}
	}

	public Dictionary findDictionaryByDictionaryName(Connection connection,String dictionaryName) throws Exception{
		try {
			String query = "select parent_id,dic_group_id,dictionary_key,dictionary_value,description,dictionary_name,active,dictionary_id from dic_dictionarys where dictionary_name = ?;";
			PreparedStatement ps = connection.prepareStatement(query);
			ps.setString(1,dictionaryName);
			ResultSet result = ps.executeQuery();
			Dictionary dictionary = new Dictionary();
			result.last();
			//Build the object.
			dictionary.setParentId(result.getLong("parent_id"));
			dictionary.setDicGroupId(result.getLong("dic_group_id"));
			dictionary.setDictionaryKey(result.getString("dictionary_key"));
			dictionary.setDictionaryValue(result.getString("dictionary_value"));
			dictionary.setDescription(result.getString("description"));
			dictionary.setDictionaryName(result.getString("dictionary_name"));
			dictionary.setActive(result.getBoolean("active"));
			dictionary.setDictionaryId(result.getLong("dictionary_id"));
			return dictionary;
		} catch (Exception e){
			e.printStackTrace();
			return null;
		}
	}

	public List<Dictionary> searchDictionarysByDictionaryName(Connection connection,String dictionaryName) throws Exception{
		try {
			String query = "select parent_id,dic_group_id,dictionary_key,dictionary_value,description,dictionary_name,active,dictionary_id from dic_dictionarys where dictionary_name like ?";
			PreparedStatement ps = connection.prepareStatement(query);
			ps.setString(1,"%"+dictionaryName+"%");
			ResultSet result = ps.executeQuery();
			ArrayList<Dictionary> list = new ArrayList<Dictionary>();
			while(result.next()) {
				//Build the list object.
				Dictionary dictionary = new Dictionary();
				dictionary.setParentId(result.getLong("parent_id"));
				dictionary.setDicGroupId(result.getLong("dic_group_id"));
				dictionary.setDictionaryKey(result.getString("dictionary_key"));
				dictionary.setDictionaryValue(result.getString("dictionary_value"));
				dictionary.setDescription(result.getString("description"));
				dictionary.setDictionaryName(result.getString("dictionary_name"));
				dictionary.setActive(result.getBoolean("active"));
				dictionary.setDictionaryId(result.getLong("dictionary_id"));
				//Build the object list.
				list.add(dictionary);
			}
			return list;
		} catch (Exception e){
			e.printStackTrace();
			return null;
		}
	}

	public List<Dictionary> listActiveDictionarys(Connection connection) throws Exception{
		try {
			String query = "select parent_id,dic_group_id,dictionary_key,dictionary_value,description,dictionary_name,active,dictionary_id from dic_dictionarys where active = true;";
			PreparedStatement ps = connection.prepareStatement(query);
			ResultSet result = ps.executeQuery();
			ArrayList<Dictionary> list = new ArrayList<Dictionary>();
			while(result.next()) {
				//Build the list object.
				Dictionary dictionary = new Dictionary();
				dictionary.setParentId(result.getLong("parent_id"));
				dictionary.setDicGroupId(result.getLong("dic_group_id"));
				dictionary.setDictionaryKey(result.getString("dictionary_key"));
				dictionary.setDictionaryValue(result.getString("dictionary_value"));
				dictionary.setDescription(result.getString("description"));
				dictionary.setDictionaryName(result.getString("dictionary_name"));
				dictionary.setActive(result.getBoolean("active"));
				dictionary.setDictionaryId(result.getLong("dictionary_id"));
				//Build the object list.
				list.add(dictionary);
			}
			return list;
		} catch (Exception e){
			e.printStackTrace();
			return null;
		}
	}

	public List<Dictionary> listAllDictionarysByPage(Connection connection,int pagesize,int pagenum) throws Exception{
		try {
			String query = "select parent_id,dic_group_id,dictionary_key,dictionary_value,description,dictionary_name,active,dictionary_id from dic_dictionarys limit ?,?;";
			int limitstart = (pagenum-1)*pagesize;
			int limitcount = pagesize;
			PreparedStatement ps = connection.prepareStatement(query);
			int pagecount = this.countDictionarysPage(connection,pagesize);
			if (pagenum <= 1) pagenum = 1;
			if (pagenum >= pagecount) pagenum = pagecount;
			ps.setInt(1,limitstart);
			ps.setInt(2,limitcount);
			ResultSet result = ps.executeQuery();
			ArrayList<Dictionary> list = new ArrayList<Dictionary>();
			while(result.next()) {
				//Build the list object.
				Dictionary dictionary = new Dictionary();
				dictionary.setParentId(result.getLong("parent_id"));
				dictionary.setDicGroupId(result.getLong("dic_group_id"));
				dictionary.setDictionaryKey(result.getString("dictionary_key"));
				dictionary.setDictionaryValue(result.getString("dictionary_value"));
				dictionary.setDescription(result.getString("description"));
				dictionary.setDictionaryName(result.getString("dictionary_name"));
				dictionary.setActive(result.getBoolean("active"));
				dictionary.setDictionaryId(result.getLong("dictionary_id"));
				//Build the object list.
				list.add(dictionary);
			}
			return list;
		} catch (Exception e){
			e.printStackTrace();
			return new ArrayList<Dictionary>();
		}
	}

	public void deleteAllDictionarys(Connection connection,String ids) throws Exception{
		String [] idArr = ids.split(",");
		for (String idString : idArr){
			Long dictionaryId = Long.valueOf(idString);
			deleteDictionary(connection,dictionaryId);
		}
	}

	public void softDeleteAllDictionarys(Connection connection,String ids) throws Exception{
		String [] idArr = ids.split(",");
		for (String idString : idArr){
			Long dictionaryId = Long.valueOf(idString);
			softDeleteDictionary(connection,dictionaryId);
		}
	}

	public Boolean toggleDictionary(Connection connection,Long dictionaryId) throws Exception{
		String query = "update dic_dictionarys set active = not active where dictionary_id = ? ";
		PreparedStatement ps = connection.prepareStatement(query);
		ps.setLong(1,dictionaryId);
		int result = ps.executeUpdate();
		if (result > 0) {
			return true;
		}
		return false;
	}

	public List<Dictionary> searchDictionarysByFieldsByPage(Connection connection,Dictionary dictionary,Long pagenum,Long pagesize) throws Exception{
		try {
		Long start = (pagenum-1)*pagesize;
		Long limit = pagesize;
		String query = " select parent_id,dic_group_id,dictionary_key,dictionary_value,description,dictionary_name,active,dictionary_id from dic_dictionarys where 1=1 ";
		if (dictionary.getParentId()!=null ){
			query += " and parent_id = ? ";
		}
		if (dictionary.getDicGroupId()!=null ){
			query += " and dic_group_id = ? ";
		}
		if (dictionary.getDictionaryKey()!=null && !"".equals(dictionary.getDictionaryKey())){
			query += " and dictionary_key like ? ";
		}
		if (dictionary.getDictionaryValue()!=null && !"".equals(dictionary.getDictionaryValue())){
			query += " and dictionary_value like ? ";
		}
		if (dictionary.getDescription()!=null && !"".equals(dictionary.getDescription())){
			query += " and description like ? ";
		}
		if (dictionary.getDictionaryName()!=null && !"".equals(dictionary.getDictionaryName())){
			query += " and dictionary_name like ? ";
		}
		if (dictionary.getActive()!=null ){
			query += " and active = ? ";
		}
		query += "limit ? offset ?;";
		//System.out.println("JerryDebug:query:"+query);
		
		Integer serial = 1;
		PreparedStatement ps = connection.prepareStatement(query);
		
		if (dictionary.getParentId()!=null ){
			ps.setLong(serial++,dictionary.getParentId());
		}
		if (dictionary.getDicGroupId()!=null ){
			ps.setLong(serial++,dictionary.getDicGroupId());
		}
		if (dictionary.getDictionaryKey()!=null && !"".equals(dictionary.getDictionaryKey())){
			ps.setString(serial++,"%"+dictionary.getDictionaryKey()+"%");
		}
		if (dictionary.getDictionaryValue()!=null && !"".equals(dictionary.getDictionaryValue())){
			ps.setString(serial++,"%"+dictionary.getDictionaryValue()+"%");
		}
		if (dictionary.getDescription()!=null && !"".equals(dictionary.getDescription())){
			ps.setString(serial++,"%"+dictionary.getDescription()+"%");
		}
		if (dictionary.getDictionaryName()!=null && !"".equals(dictionary.getDictionaryName())){
			ps.setString(serial++,"%"+dictionary.getDictionaryName()+"%");
		}
		if (dictionary.getActive()!=null ){
			ps.setBoolean(serial++,dictionary.getActive());
		}
		ps.setLong(serial++, limit);
		ps.setLong(serial++,start);
		
		ResultSet result = ps.executeQuery();
		ArrayList<Dictionary> list = new ArrayList<Dictionary>();
		while(result.next()) {
			//Build the list object.
			Dictionary dictionary2 = new Dictionary();
			dictionary2.setParentId(result.getLong("parent_id"));
			dictionary2.setDicGroupId(result.getLong("dic_group_id"));
			dictionary2.setDictionaryKey(result.getString("dictionary_key"));
			dictionary2.setDictionaryValue(result.getString("dictionary_value"));
			dictionary2.setDescription(result.getString("description"));
			dictionary2.setDictionaryName(result.getString("dictionary_name"));
			dictionary2.setActive(result.getBoolean("active"));
			dictionary2.setDictionaryId(result.getLong("dictionary_id"));
			//Build the object list.
			list.add(dictionary2);
		}
		return list;
		} catch (Exception e){
			e.printStackTrace();
			return null;
		}
	}

	public int countDictionarysPage(Connection connection,int pagesize) throws Exception{
		try {
			int pagecount = 1;
			double recordcount = 0;
			String query = "select count(dictionary_id) as countNum from dic_dictionarys;";
			PreparedStatement ps = connection.prepareStatement(query);
			ResultSet result = ps.executeQuery();
			while(result.next()) {
				recordcount = result.getDouble("countNum");
				pagecount = (int)Math.ceil(recordcount/pagesize);
				if (pagecount <= 1)pagecount = 1;
			}
			return pagecount;
		} catch (Exception e){
			e.printStackTrace();
			return 1;
		}
	}

	public Long countActiveDictionaryRecords(Connection connection) throws Exception{
		String query = "select count(dictionary_id) as countNum from dic_dictionarys where active = true;";
		PreparedStatement ps = connection.prepareStatement(query);
		ResultSet result = ps.executeQuery();
		Long recordcount = 0L;
		while(result.next()) {
			recordcount = result.getLong("countNum");
		}
		return recordcount;
	}

	public Long countAllDictionaryRecords(Connection connection) throws Exception{
		String query = "select count(dictionary_id) as countNum from dic_dictionarys;";
		PreparedStatement ps = connection.prepareStatement(query);
		ResultSet result = ps.executeQuery();
		Long recordcount = 0L;
		while(result.next()) {
			recordcount = result.getLong("countNum");
		}
		return recordcount;
	}

	public Long countSearchDictionarysByFieldsRecords(Connection connection,Dictionary dictionary){
		try {
		String query = " select count(*) countSum from dic_dictionarys where 1=1 ";
		if (dictionary.getParentId()!=null ){
			query += " and parent_id = ? ";
		}
		if (dictionary.getDicGroupId()!=null ){
			query += " and dic_group_id = ? ";
		}
		if (dictionary.getDictionaryKey()!=null && !"".equals(dictionary.getDictionaryKey())){
			query += " and dictionary_key like ? ";
		}
		if (dictionary.getDictionaryValue()!=null && !"".equals(dictionary.getDictionaryValue())){
			query += " and dictionary_value like ? ";
		}
		if (dictionary.getDescription()!=null && !"".equals(dictionary.getDescription())){
			query += " and description like ? ";
		}
		if (dictionary.getDictionaryName()!=null && !"".equals(dictionary.getDictionaryName())){
			query += " and dictionary_name like ? ";
		}
		if (dictionary.getActive()!=null ){
			query += " and active = ? ";
		}
		query += ";";
		//System.out.println("JerryDebug:query:"+query);
		
		int serial = 1;
		PreparedStatement ps = connection.prepareStatement(query);
		
		if (dictionary.getParentId()!=null ){
			ps.setLong(serial++,dictionary.getParentId());
		}
		if (dictionary.getDicGroupId()!=null ){
			ps.setLong(serial++,dictionary.getDicGroupId());
		}
		if (dictionary.getDictionaryKey()!=null && !"".equals(dictionary.getDictionaryKey())){
			ps.setString(serial++,"%"+dictionary.getDictionaryKey()+"%");
		}
		if (dictionary.getDictionaryValue()!=null && !"".equals(dictionary.getDictionaryValue())){
			ps.setString(serial++,"%"+dictionary.getDictionaryValue()+"%");
		}
		if (dictionary.getDescription()!=null && !"".equals(dictionary.getDescription())){
			ps.setString(serial++,"%"+dictionary.getDescription()+"%");
		}
		if (dictionary.getDictionaryName()!=null && !"".equals(dictionary.getDictionaryName())){
			ps.setString(serial++,"%"+dictionary.getDictionaryName()+"%");
		}
		if (dictionary.getActive()!=null ){
			ps.setBoolean(serial++,dictionary.getActive());
		}
		
		ResultSet result = ps.executeQuery();
		Long countSum = 0L;
		while(result.next()) {
			countSum = result.getLong("countSum");
		}
		return countSum;
		} catch (Exception e){
			e.printStackTrace();
			return 0L;
		}
	}

}
